package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import connectionMSqL.MySQLConn;
import entity.Aluno;
import entity.Professor;

public class AlunoDAO {

	public static boolean insert(Aluno a) {
		boolean salvou = false;

		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			int paramIndex = 1;
			ps = conn
					.prepareStatement("INSERT INTO Aluno (cpf,nome,endereco,email,telefone)"
							+ "VALUES (?,?,?,?,?)");
			ps.setString(paramIndex++, a.getCpf());
			ps.setString(paramIndex++, a.getNome());
			ps.setString(paramIndex++, a.getEndereco());
			ps.setString(paramIndex++, a.getEmail());
			ps.setString(paramIndex++, a.getTelefone());

			ps.executeUpdate();
			salvou = true;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					ps.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return salvou;
	}
public static Aluno createAluno(ResultSet rs) throws SQLException {
		
		Aluno aluno = new Aluno();
		aluno.setMatricula(rs.getInt("matricula"));
		aluno.setCpf(rs.getString("cpf"));
		aluno.setNome(rs.getString("nome"));
		aluno.setEndereco(rs.getString("endereco"));
		aluno.setEmail(rs.getString("email"));
		aluno.setTelefone(rs.getString("telefone"));

		return aluno;
	}

	public static List<Aluno> getAll() {
		List<Aluno> alunos = new ArrayList<Aluno>();
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT * FROM Aluno");
			rs = ps.executeQuery();
			while (rs.next()) {

				Aluno a = createAluno(rs);
				alunos.add(a);
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();

			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}

		return alunos;
	}

	public static boolean update(Aluno a) {
		
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			String query = ("UPDATE aluno" + " SET cpf='" + a.getCpf() + "'"
					+ ", nome='" + a.getNome() + "'" + ",endereco ='"
					+ a.getEndereco() + "'" + ",email='" + a.getEmail() + "'"
					+ ", telefone='" + a.getTelefone() + "'" + " WHERE matricula =" + a.getMatricula());
			
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
			return true;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return false;
	}

	public static List<Aluno> findByMatricula(int matricula){
		
		List<Aluno> alunos = new ArrayList<Aluno>();
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps = conn.prepareStatement(" SELECT * FROM aluno WHERE matricula="+ matricula);
			rs = ps.executeQuery();
			if(rs.next()){
				
				Aluno a = createAluno(rs);
				alunos.add(a);
			}
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) rs.close();
				if (ps != null) ps.close();
				if (conn != null) conn.close();
			} 
			catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		
		return alunos;
	}
	
	public static boolean delete(int matricula) {
		
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("DELETE FROM aluno WHERE matricula="+ matricula);
			ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
			} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();

			} catch (SQLException e) {
				System.out.println(e.getMessage());
				return false;
			}
		}

		return true;
	}

}
